package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ModelLayer.Invoice;

public class DBInvoice implements IFDBInvoice {

	private Connection con;

	public DBInvoice() {

		con = DbConnection1.getInstance().getDBcon();
	}

	@Override
	public Invoice findInvoice(int invID, boolean retriveAssociation) {
		String wClause = "  Inv_No = '" + invID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	private Invoice singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		Invoice invoiceObj = new Invoice();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the OrderLine from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				invoiceObj = buildInvoice(results);

				stmt.close();

			} else { // no OrderLine was found
				invoiceObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return invoiceObj;
	}

	private Invoice buildInvoice(ResultSet results) {
		Invoice invoiceObj = new Invoice();
		try {

			invoiceObj.setInvNo(results.getInt(1));
			invoiceObj.setPayDate(results.getString(2));
			invoiceObj.setfAmount(results.getDouble(3));

		} catch (Exception e) {
			System.out.println("error in building the invoice object");
		}
		return invoiceObj;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT Inv_NO ,PayDate ,FinAmount FROM Invoice";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	@Override
	public int insert(Invoice inv) throws Exception {
		int rc = -1;

		String query = "INSERT INTO Invoice (Inv_NO ,PayDate ,FinAmount) VALUES('"
				+ inv.getInvNo()
				+ "', '"
				+ inv.getPayDate()
				+ "', "
				+ inv.getfAmount() + ")";

		System.out.println("insert : " + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("problem while inserting invoice");
			throw new Exception("insertion was not correct");
		}

		return rc;
	}

	@Override
	public int update(Invoice inv) {
		int rc = -1;

		String query = "UPDATE Invoice SET " + "PayDate ='" + inv.getPayDate()
				+ "' ,FinAmount='" + inv.getfAmount() + "'" +

				" WHERE Inv_NO = '" + inv.getInvNo() + "'";

		System.out.println("Update query:" + query);
		try { // update OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in invoice  db: " + ex);
		}
		return (rc);
	}

	@Override
	public int delete(Invoice inv) {
		int rc = -1;

		String query = "DELETE FROM Invoice WHERE Inv_NO= '" + inv.getInvNo()
				+ "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in invoice db: " + ex);
		}
		return (rc);
	}

}
